package com.example.demo.mapper;
import com.example.demo.domain.dao.DeletedSchedule;
import com.example.demo.domain.dao.HbutClassSchedule;
import com.example.demo.domain.dao.HbutEtcClassSchedule;
import com.example.demo.domain.dao.OptionalCourse;
import com.example.demo.domain.dao.OwnSchedule;
import com.example.demo.domain.dao.OwnScheduleTime;
import com.example.demo.domain.dto.UserOptionalCourse;
import com.example.demo.domain.result.AutoIncreasedId;
import java.sql.Date;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @author lizijian
 */
@Mapper
@Repository
public interface ScheduleDao {




    @Insert("INSERT INTO  hbut_class_schedule (name,data,college_name, grade) VALUES (#{className},#{data},#{collegeName},#{grade})")
    void insertSchedule(@Param("className")String className, @Param("data") String data, @Param("collegeName") String collegeName, @Param("grade") String grade);


    @Select("SELECT * FROM hbut_class_schedule WHERE name =#{className}")
    @Result(column = "update_time",property = "updateTime")
    HbutClassSchedule queryScheduleByClassName(@Param("className") String className);


    @Results({@Result(column = "update_time",property = "updateTime"),
            @Result(column = "class_id",property = "classId")
    })

    @Select("SELECT * FROM hbut_etc_class_schedule WHERE class_id=#{classId}")
    HbutEtcClassSchedule queryHbutEtcScheduleByClassId(Integer classId);

    @Update("UPDATE hbut_etc_class_schedule SET data = #{data},update_time= #{updateTime} WHERE class_id =#{classId}")
    void updateHbutEtcSchedule(@Param("classId") Integer classId,@Param("data") String data,@Param("updateTime") Date updateTime);

    @Insert("INSERT INTO hbut_etc_class_schedule (class_id,name,data) VALUES (#{classId},#{className},#{data})")
    void addHbutEtcSchedule(@Param("classId") Integer classId,@Param("data") String data,@Param("className") String className);

    /**
     * 清空全校课表
     */
    @Delete("TRUNCATE TABLE hbut_class_schedule")
    void deleteSchedule();

    /**
     * 模糊查询班级名
     * @param className 班级名
     * @return 符合条件的班级名列表
     */
    @Select("SELECT name FROM hbut_class_schedule WHERE name LIKE #{className}")
    List<String> fuzzyQueryClassName(String className);


    /**
     *  查询指定用户的自定义课表
     */
    @Results({@Result(column = "user_id",property = "userId"),
    @Result(column = "create_time",property = "createTime")})
    @Select("SELECT * FROM own_schedule WHERE user_id= #{userId}")
    List<OwnSchedule> getOwnScheduleByUserId(Integer userId);


    @Select("SELECT * FROM own_schedule_time WHERE own_schedule_id = #{ownScheduleId}")
    List<OwnScheduleTime> getOwnScheduleTimeByScheduleId(Integer ownScheduleId);


    /**
     *  增加自定义课程
     */
    @Insert("INSERT INTO own_schedule (user_id,name) VALUES (#{userId},#{name})")
    @Options(keyColumn = "id",keyProperty = "autoIncreasedId.id",useGeneratedKeys = true)
    void addOwnSchedule(@Param("autoIncreasedId") AutoIncreasedId autoIncreasedId, @Param("userId") Integer userId,@Param("name") String name);

    /**
     *  增加自定义课程时间
     */
    @Insert("INSERT INTO own_schedule_time (own_schedule_id,address,weeks,day,lesson) VALUES (#{ownScheduleId},#{address},#{weeks},#{day},#{lesson})")
    void addOwnScheduleTime(@Param("ownScheduleId") Integer ownScheduleId,@Param("address") String address,@Param("weeks") String weeks,
        @Param("day") Integer day,@Param("lesson") Integer lesson);

    /**
     *  修改自定义课程时间
     */
    @Update("UPDATE own_schedule_time SET address =#{address} ,weeks=#{weeks} , `day` =#{day} ,lesson =#{lesson}  WHERE id = #{scheduleTimeId}")
    void updateOwnScheduleTime(@Param("scheduleTimeId") Integer scheduleTimeId,@Param("address") String address,
        @Param("weeks") String weeks,@Param("day") Integer day,@Param("lesson") Integer lesson);

    /**
     * 删除指定自定义课程
     */
    @Delete("DELETE  FROM own_schedule WHERE  id = #{id}")
    void deleteOwnScheduleByScheduleId(@Param("id") Integer scheduleId);

    /**
     *  删除指定自定义课程的时间
     */
    @Delete("DELETE FROM own_schedule_time WHERE own_schedule_id =#{scheduleId}")
    void deleteOwnScheduleTimeByScheduleId(Integer scheduleId);

    /**
     *  删除指定用户的所有自定义课程
     */
    @Delete("DELETE  FROM own_schedule WHERE user_id=#{userId}")
    void deleteOwnScheduleByUserId(Integer userId);

    /**
     *  删除指定用户的所有自定义课程时间
     */
    @Delete("DELETE FROM own_schedule_time WHERE own_schedule_id IN (SELECT id FROM own_schedule WHERE user_id = #{userId})")
    void deleteOwnScheduleTimeByUserId(Integer userId);

    /**
     * 查找指定用户删除的课程
     */
    @Select("SELECT * FROM own_schedule_delete WHERE user_id = #{userId}")
    List<DeletedSchedule> getDeletedScheduleByUserId(Integer userId);


    /**
     *  删除教务系统课程
     */
    @Insert("INSERT INTO own_schedule_delete (schedule_name,user_id) VALUES (#{scheduleName},#{userId})")
    void addDeletedSchedule(@Param("scheduleName") String scheduleName,@Param("userId") Integer userId);

    /**
     *  根据课程名查询自定义课程
     */
    @Select("SELECT * FROM own_schedule WHERE user_id=#{userId} AND `name` =#{name} LIMIT 1")
    OwnSchedule getOwnScheduleByName(@Param("userId") Integer userId,@Param("name") String name);


    @Delete("DELETE FROM own_schedule_delete WHERE id = #{deletedScheduleId}")
    void recoverDeletedSchedule(Integer deletedScheduleId);

    @Select("SELECT * FROM optional_course WHERE course_name = #{courseName}")
    List<OptionalCourse> getOptionalCourseByCourseName(String courseName);


    @Select("SELECT optional_course.*,user_optional_course.update_time  FROM user_optional_course,optional_course WHERE user_optional_course.username=#{username} AND user_optional_course.optional_course_name = optional_course.course_name")
    List<UserOptionalCourse> getOptionalCourseByUsername(String username);

    @Delete("DELETE FROM user_optional_course WHERE user_optional_course.username = #{username}")
    void deleteUserOptionalCourse(String username);

    @Delete("DELETE FROM user_arbitrarily_course WHERE username = #{username}")
    void deleteArbitraryCourse(String username);

    @Insert("INSERT INTO user_arbitrarily_course (username,course_name,address,weeks,day,lesson) VALUES (#{username},#{courseName},#{address},#{weeks},#{day},#{lesson})")
    void addUserArbitraryCourse(@Param("username") String username,@Param("courseName") String courseName,
        @Param("address") String address,@Param("weeks") String weeks,@Param("day") int day,@Param("lesson") int lesson);

    @Insert("INSERT INTO user_optional_course (username,optional_course_name) VALUES (#{username},#{courseName})")
    void addUserOptionalCourse(@Param("username") String username,@Param("courseName") String courseName);

    @Select("SELECT * FROM user_arbitrarily_course WHERE username = #{username}")
    List<UserOptionalCourse> getArbitraryCourse(String username);


}
